from config import DB


# 根据供应商名称查询dz_vendor_info表中的供应商类型和ID
def get_vendor_type_and_id(vendor_name):
    """
    根据供应商名称查询供应商的结算类型和ID

    Args:
        vendor_name (str): 供应商名称

    Returns:
        tuple: (settlement_type, vendor_id) 或 None
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()
        query = "SELECT settlement_type, vendor_id FROM dz_vendor_info WHERE vendor_name = %s"
        cursor.execute(query, (vendor_name,))
        result = cursor.fetchone()
        return result
    except Exception as e:
        print(f"查询供应商信息时发生错误: {str(e)}")
        return None
    finally:
        if connection:
            connection.close()


def get_vendor_data_by_type(vendor_name):
    """
    根据供应商名称获取对应的数据，自动判断查询哪张表
    月结供应商查询dz_purchase_detail表，实销实结供应商查询dz_real_sales_detail表

    Args:
        vendor_name (str): 供应商名称

    Returns:
        list: 查询结果列表
    """
    try:
        # 先获取供应商类型和ID
        vendor_info = get_vendor_type_and_id(vendor_name)
        if not vendor_info:
            print(f"未找到供应商: {vendor_name}")
            return []

        settlement_type, vendor_id = vendor_info
        print(f"供应商 {vendor_name} 的结算类型: {settlement_type}, ID: {vendor_id}")

        # 根据结算类型判断查询哪张表
        if settlement_type in ['月结', 'OEM月结']:
            return _get_purchase_detail_data(vendor_id, vendor_name)
        elif settlement_type in ['实销实结', 'OEM实销实结']:
            return _get_real_sales_detail_data(vendor_id, vendor_name)
        else:
            print(f"未知的结算类型: {settlement_type}")
            return []

    except Exception as e:
        print(f"查询供应商数据时发生错误: {str(e)}")
        return []


def _get_purchase_detail_data(vendor_id, vendor_name):
    """
    查询月结明细数据

    Args:
        vendor_id (str): 供应商ID
        vendor_name (str): 供应商名称

    Returns:
        list: 采购明细数据列表
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()
        query = """
        SELECT 
            goods_doc_no, vendor_id, vend_name, order_num, 
            goods_no, goods_name, sku_id, quantity, 
            tax_price, transHasTaxAmount, in_out_type_name, create_time
        FROM dz_purchase_detail 
        WHERE vend_id = %s
        ORDER BY create_time DESC
        """
        cursor.execute(query, (vendor_id,))
        result = cursor.fetchall()
        print(f"从采购明细表查询到 {len(result) if result else 0} 条数据")
        return result if result else []

    except Exception as e:
        print(f"查询采购明细数据时发生错误: {str(e)}")
        return []
    finally:
        if connection:
            connection.close()


def _get_real_sales_detail_data(vendor_id, vendor_name):
    """
    从dz_real_sales_detail表查询实销实结供应商的销售明细数据

    Args:
        vendor_id (str): 供应商ID
        vendor_name (str): 供应商名称

    Returns:
        list: 销售明细数据列表
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()
        query = """
        SELECT 
            vendor_id, vend_name, goods_no, goods_name, 
            sku_id, last_purchase_price, goodsQty, 
            warehouse_name, create_time
        FROM dz_real_sales_detail 
        WHERE vendor_id = %s
        ORDER BY create_time DESC
        """
        cursor.execute(query, (vendor_id,))
        result = cursor.fetchall()
        print(f"从实销实结明细表查询到 {len(result) if result else 0} 条数据")
        return result if result else []

    except Exception as e:
        print(f"查询实销实结明细数据时发生错误: {str(e)}")
        return []
    finally:
        if connection:
            connection.close()


def get_vendor_summary_by_type(vendor_name):
    """
    根据供应商名称获取汇总数据

    Args:
        vendor_name (str): 供应商名称

    Returns:
        dict: 汇总数据字典
    """
    try:
        vendor_info = get_vendor_type_and_id(vendor_name)
        if not vendor_info:
            return {"error": f"未找到供应商: {vendor_name}"}

        settlement_type, vendor_id = vendor_info

        if settlement_type in ['月结', 'OEM月结']:
            return _get_purchase_summary(vendor_id, vendor_name, settlement_type)
        elif settlement_type in ['实销实结', 'OEM实销实结']:
            return _get_sales_summary(vendor_id, vendor_name, settlement_type)
        else:
            return {"error": f"未知的结算类型: {settlement_type}"}

    except Exception as e:
        return {"error": f"查询汇总数据时发生错误: {str(e)}"}


def _get_purchase_summary(vendor_id, vendor_name, settlement_type):
    """
    获取月结供应商的采购汇总数据
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()

        # 查询汇总数据
        summary_query = """
        SELECT 
            COUNT(*) as total_records,
            SUM(quantity) as total_quantity,
            SUM(transHasTaxAmount) as total_amount,
            AVG(tax_price) as avg_price
        FROM dz_purchase_detail 
        WHERE vend_id = %s
        """
        cursor.execute(summary_query, (vendor_id,))
        summary_result = cursor.fetchone()

        # 查询货品明细数据
        goods_query = """
        SELECT 
            goods_no,
            goods_name,
            sku_id,
            tax_price,
            SUM(quantity) as total_qty
        FROM dz_purchase_detail 
        WHERE vend_id = %s
        GROUP BY goods_no, goods_name, sku_id, tax_price
        ORDER BY total_qty DESC
        """
        cursor.execute(goods_query, (vendor_id,))
        goods_results = cursor.fetchall()

        # 构建货品列表
        goods_list = []
        for goods in goods_results:
            goods_list.append({
                "goods_no": goods[0],
                "goods_name": goods[1],
                "sku_id": goods[2],
                "tax_price": float(goods[3]) if goods[3] else 0.0,
                "total_qty": float(goods[4]) if goods[4] else 0.0
            })

        return {
            "vendor_name": vendor_name,
            "vendor_id": vendor_id,
            "settlement_type": settlement_type,
            "data_source": "采购明细表",
            "total_records": summary_result[0] if summary_result else 0,
            "total_quantity": float(summary_result[1]) if summary_result and summary_result[1] else 0.0,
            "total_amount": float(summary_result[2]) if summary_result and summary_result[2] else 0.0,
            "avg_price": float(summary_result[3]) if summary_result and summary_result[3] else 0.0,
            "total_goods_count": len(goods_list),
            "goods_list": goods_list
        }

    except Exception as e:
        return {"error": f"查询采购汇总数据时发生错误: {str(e)}"}
    finally:
        if connection:
            connection.close()


def _get_sales_summary(vendor_id, vendor_name, settlement_type):
    """
    获取实销实结供应商的销售汇总数据
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()

        # 查询汇总数据
        summary_query = """
        SELECT 
            COUNT(*) as total_records,
            SUM(goodsQty) as total_sales_qty,
            AVG(last_purchase_price) as avg_purchase_price
        FROM dz_real_sales_detail 
        WHERE vendor_id = %s
        """
        cursor.execute(summary_query, (vendor_id,))
        summary_result = cursor.fetchone()

        # 查询货品明细数据
        goods_query = """
        SELECT 
            goods_no,
            goods_name,
            sku_id,
            last_purchase_price,
            SUM(goodsQty) as total_qty
        FROM dz_real_sales_detail 
        WHERE vendor_id = %s
        GROUP BY goods_no, goods_name, sku_id, last_purchase_price
        ORDER BY total_qty DESC
        """
        cursor.execute(goods_query, (vendor_id,))
        goods_results = cursor.fetchall()

        # 构建货品列表
        goods_list = []
        for goods in goods_results:
            goods_list.append({
                "goods_no": goods[0],
                "goods_name": goods[1],
                "sku_id": goods[2],
                "last_purchase_price": float(goods[3]) if goods[3] else 0.0,
                "total_qty": float(goods[4]) if goods[4] else 0.0
            })

        return {
            "vendor_name": vendor_name,
            "vendor_id": vendor_id,
            "settlement_type": settlement_type,
            "data_source": "实销实结明细表",
            "total_records": summary_result[0] if summary_result else 0,
            "total_sales_qty": float(summary_result[1]) if summary_result and summary_result[1] else 0.0,
            "avg_purchase_price": float(summary_result[2]) if summary_result and summary_result[2] else 0.0,
            "total_goods_count": len(goods_list),
            "goods_list": goods_list
        }

    except Exception as e:
        return {"error": f"查询销售汇总数据时发生错误: {str(e)}"}
    finally:
        if connection:
            connection.close()


def normalize_vendor_data(data):
    """
    统一供应商数据字段名称
    将采购明细表(dz_purchase_detail)和实销实结明细表(dz_real_sales_detail)的字段统一
    移除不需要的原始字段，只保留统一后的字段

    采购明细表字段映射:
    - quantity -> unified_quantity (数量)
    - tax_price -> unified_unit_price (单价)
    - transHasTaxAmount -> unified_amount (金额)

    实销实结明细表字段映射:
    - goodsQty -> unified_quantity (数量)
    - last_purchase_price -> unified_unit_price (单价)

    Args:
        data (dict): 原始供应商数据

    Returns:
        dict: 标准化后的供应商数据
    """
    if 'error' in data:
        return data

    # 创建新的数据字典，只保留需要的字段
    normalized_data = {
        "vendor_name": data.get("vendor_name"),
        "vendor_id": data.get("vendor_id"),
        "settlement_type": data.get("settlement_type"),
        "data_source": data.get("data_source"),
        "total_goods_count": data.get("total_goods_count", 0)
    }

    # 根据数据源进行字段统一
    if data.get('data_source') == '采购明细表':
        # 采购明细表字段映射
        if 'total_quantity' in data:
            normalized_data['unified_total_quantity'] = data['total_quantity']
        if 'total_amount' in data:
            normalized_data['unified_total_amount'] = data['total_amount']
        if 'avg_price' in data:
            normalized_data['unified_avg_unit_price'] = data['avg_price']

        # 统一goods_list中的字段
        if 'goods_list' in data and data['goods_list']:
            normalized_goods_list = []
            for goods in data['goods_list']:
                normalized_goods = {
                    "goods_no": goods.get("goods_no"),
                    "goods_name": goods.get("goods_name"),
                    "sku_id": goods.get("sku_id")
                }
                # 将tax_price统一为unified_unit_price
                if 'tax_price' in goods:
                    normalized_goods['unified_unit_price'] = goods['tax_price']
                # 将total_qty统一为unified_quantity
                if 'total_qty' in goods:
                    normalized_goods['unified_quantity'] = goods['total_qty']
                normalized_goods_list.append(normalized_goods)
            normalized_data['goods_list'] = normalized_goods_list

    elif data.get('data_source') == '实销实结明细表':
        # 实销实结明细表字段映射
        if 'total_sales_qty' in data:
            normalized_data['unified_total_quantity'] = data['total_sales_qty']
        if 'avg_purchase_price' in data:
            normalized_data['unified_avg_unit_price'] = data['avg_purchase_price']
        # 实销实结表没有总金额，设置为0或计算值
        normalized_data['unified_total_amount'] = 0.0

        # 统一goods_list中的字段
        if 'goods_list' in data and data['goods_list']:
            normalized_goods_list = []
            for goods in data['goods_list']:
                normalized_goods = {
                    "goods_no": goods.get("goods_no"),
                    "goods_name": goods.get("goods_name"),
                    "sku_id": goods.get("sku_id")
                }
                # 将last_purchase_price统一为unified_unit_price
                if 'last_purchase_price' in goods:
                    normalized_goods['unified_unit_price'] = goods['last_purchase_price']
                # 将total_qty统一为unified_quantity
                if 'total_qty' in goods:
                    normalized_goods['unified_quantity'] = goods['total_qty']
                normalized_goods_list.append(normalized_goods)
            normalized_data['goods_list'] = normalized_goods_list

    return normalized_data


def update_paid_amount(update_data):
    """
    更新累计已付金额
    
    Args:
        update_data: 更新数据，支持两种格式：
                    1. 嵌套格式：{'vendor_name': str, 'details': [...]}
                    2. 列表格式：[{...}, {...}, ...]
    
    Returns:
        dict: 包含success, message, updated_count的结果字典
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()
        updated_count = 0

        # 处理嵌套格式数据
        if isinstance(update_data, dict) and 'details' in update_data:
            vendor_name = update_data.get('vendor_name', '')
            details = update_data.get('details', [])

            for detail in details:
                if not _validate_update_record(detail):
                    continue

                # 执行更新操作
                if _execute_paid_amount_update(cursor, detail):
                    updated_count += 1

        # 处理列表格式数据
        elif isinstance(update_data, list):
            for detail in update_data:
                if not _validate_update_record(detail):
                    continue

                # 执行更新操作
                if _execute_paid_amount_update(cursor, detail):
                    updated_count += 1
        else:
            return {
                'success': False,
                'message': '不支持的数据格式',
                'updated_count': 0
            }

        # 提交事务
        connection.commit()

        return {
            'success': True,
            'message': f'成功更新 {updated_count} 条记录的累计已付金额',
            'updated_count': updated_count
        }

    except Exception as e:
        if connection:
            connection.rollback()
        return {
            'success': False,
            'message': f'更新累计已付金额时发生错误: {str(e)}',
            'updated_count': 0
        }
    finally:
        if connection:
            connection.close()


def _validate_update_record(record):
    """
    验证更新记录的有效性
    
    Args:
        record (dict): 待验证的记录
        
    Returns:
        bool: 记录是否有效
    """
    required_fields = ['vendor_id', 'paid_amount']

    if not isinstance(record, dict):
        print(f"记录格式错误，必须是字典类型: {record}")
        return False

    for field in required_fields:
        if field not in record:
            print(f"记录缺少必要字段 {field}: {record}")
            return False

    # 验证金额格式
    try:
        float(record['paid_amount'])
    except (ValueError, TypeError):
        print(f"付款金额格式错误: {record.get('paid_amount')}")
        return False

    return True


def _execute_paid_amount_update(cursor, record):
    """
    执行单条记录的累计已付金额更新
    
    Args:
        cursor: 数据库游标
        record (dict): 包含更新信息的记录
        
    Returns:
        bool: 更新是否成功
    """
    try:
        vendor_id = record['vendor_id']
        paid_amount = float(record['paid_amount'])

        # 根据供应商类型确定更新的表
        vendor_info = get_vendor_type_and_id_by_id(vendor_id)
        if not vendor_info:
            print(f"未找到供应商ID: {vendor_id}")
            return False

        settlement_type = vendor_info[0]  # settlement_type

        # 根据结算类型选择更新表
        if settlement_type in ['月结', 'OEM月结']:
            # 更新采购明细表
            update_query = """
            UPDATE dz_purchase_detail 
            SET cumulative_paid_amount = %s
            WHERE vend_id = %s
            """
        elif settlement_type in ['实销实结', 'OEM实销实结']:
            # 更新实销实结明细表
            update_query = """
            UPDATE dz_real_sales_detail 
            SET cumulative_paid_amount = %s
            WHERE vendor_id = %s
            """
        else:
            print(f"未知的结算类型: {settlement_type}")
            return False

        # 执行更新
        cursor.execute(update_query, (paid_amount, vendor_id))

        if cursor.rowcount > 0:
            print(f"成功更新供应商 {vendor_id} 的累计已付金额: {paid_amount}")
            return True
        else:
            print(f"未找到供应商 {vendor_id} 的记录进行更新")
            return False

    except Exception as e:
        print(f"更新供应商 {record.get('vendor_id')} 的累计已付金额时发生错误: {str(e)}")
        return False


def get_vendor_type_and_id_by_id(vendor_id):
    """
    根据供应商ID查询供应商的结算类型和名称
    
    Args:
        vendor_id (str): 供应商ID
        
    Returns:
        tuple: (settlement_type, vendor_name) 或 None
    """
    connection = None
    try:
        connection = DB()
        cursor = connection.cursor()
        query = "SELECT settlement_type, vendor_name FROM dz_vendor_info WHERE vendor_id = %s"
        cursor.execute(query, (vendor_id,))
        result = cursor.fetchone()
        return result
    except Exception as e:
        print(f"查询供应商信息时发生错误: {str(e)}")
        return None
    finally:
        if connection:
            connection.close()
